/*
 * Created on Jun 23, 2004
 */
package com.scalar.chiptrack.operations.orderscheduling.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.LotNumberQtyAndDateOut;
import com.scalar.chiptrack.sales.LineItem;
import com.scalar.chiptrack.sales.dao.SalesOrderDAO;
import com.scalar.chiptrack.sales.dao.NewSalesOrderDAO;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.users.dao.UserDAO;
import com.scalar.chiptrack.operations.Inventory;
import com.scalar.chiptrack.operations.process.dao.ProductDao;

/**
 * @author Ravikanth
**/
public class OrderSchedulingDAO  implements Constants
{
	public final static String SUBJECT_MISSED_SALESORDER_SCHEDULE_DATE = "Missed Sales Order by Schedule Date";
	
	public static final int BY_SCHEDULE_DATE = 0;
	public static final int BY_REQUESTED_DATE = 1;

	public static final int RESERVED_LOT_QUANTITY = 0;
	public static final int ENTIRE_LOT_QUANTITY = 1;
	public static final int ISSUED_LOT_QUANTITY = 2;
	
	public static LineItem scheduleLineItem ( String lineItemNumber, String salesOrderId, String mfgPartNumber, String requestedScheduleDate, int requestedQty, LinkedHashMap info ) throws ScalarException 
	{
		LineItem lineItem 	= null;
		Date now 			= new Date();
		boolean success 	= false;

		DBTransaction dbTransaction     = null;
		Connection conn  			    = null;
		ResultSet rs     			    = null;
		PreparedStatement pstmt 	    = null;

		Date requestedDate 			    = null;
		Date systemSuggestedDate 	    = null;
		long daysToRequestedDate 	    = 0;
		int transitTimeFromFabToFG 	    = 0;
		int fgProcessStep_Id 		    = -1;
		int availableQty 			    = 0;
        int customerID                  = -1;
        int customerJitWindow           = 0;
        int clientJitWindow    = 0;

		try
		{
			// Start transaction
			dbTransaction = DBTransaction.getInstance();
			dbTransaction.begin();

			// Step 1
			// Load Line Item
			lineItem = SalesOrderDAO.loadOrderLineItem ( lineItemNumber, salesOrderId );
			
			// Setting the requested Quantity Explicitly from the Parameters 
			// because the Quantity in the Lineitem is the Total Quantity ordered
			// and we need to remove the Shipped Qty
			lineItem.setQuantity( requestedQty );   
			
			// Step 2
			// Get the Finished Goods Process Step Id
			fgProcessStep_Id  = getFinshedGoodsProcessStepId( dbTransaction, lineItem.getProductId() );

			// Step 3
			// Get new Schedule Date
			requestedDate  = StringUtils.format ( requestedScheduleDate.trim() );
            // step 4
            //Get customerID
            customerID     = NewSalesOrderDAO.getCustomerID( dbTransaction, salesOrderId);
            //step 5
            //get customerJITWindow value
            customerJitWindow = NewSalesOrderDAO.getJitWindow( dbTransaction, customerID);
            //step6
            //Get the clientJITWindow value
            String clientaddressId =  UserDAO.getClientAddressId();

            if(clientaddressId != null)
			{
				StringTokenizer st = new StringTokenizer(clientaddressId,"@");
				try
				{
				    st.nextToken();
				    st.nextToken();
					st.nextToken();
                    clientJitWindow   = Integer.parseInt(st.nextToken());
				}catch(NoSuchElementException e)
				{
				}
			}

			//Deduct  days from requested dock date for shipping
			Calendar calDate = new GregorianCalendar();
			calDate.setTime( requestedDate );

            if( customerJitWindow > 0)
            {
               calDate.add( Calendar.DATE, - customerJitWindow );
            }
            else if( clientJitWindow > 0)
            {
               calDate.add( Calendar.DATE, - clientJitWindow );
            }
            else
            {
			    calDate.add( Calendar.DATE, -5 );
            }

			requestedDate = calDate.getTime();
			
			// Compare Requested date with the Present Date 
			// if the Requested Date is less than Present Date Set the Requested Date to Present Date    
			if( now.compareTo( requestedDate ) > 0 )
			{
				requestedDate = now;
			}
			
			// Release all the reserved lots for this Line Item.
			// This is required if the Lots are already reserved for the Line Item and 
			// the user comes to order scheduling and clicks on reschedule again with out relesing the lots. 
			releaseReservedLots ( dbTransaction, lineItem.getLineItemNo(), lineItem.getSalesOrderId(), fgProcessStep_Id );			
			
			boolean hasRequiredQty 	= false;
			Calendar sysSuggCal 	= null;

			try
			{
				if ( dbTransaction == null )
				{
					conn = DataSource.getConnection();
				}
				else
				{
					conn = dbTransaction.getConnection();
				}

				// Check if the Quantity is available from the Requested Date
				//pstmt = conn.prepareStatement( "SELECT Sum( Fg_Qty - Qty_Allocated ) as Qty_Available from Wip_Master where Fg_ProcessStep_ID = ? and Fg_date <= ?" );
				pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.orderscheduling.availablequantity.requesteddate" ) );
				pstmt.setInt ( 1, fgProcessStep_Id );
				pstmt.setDate( 2, StringUtils.getSQLDate( requestedDate ) );
				
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					availableQty = rs.getInt( "Qty_Available" );
				}
				
				// Check if the Quantity is available set the System Suggested Date to Requested Date
				if( availableQty >= requestedQty )
				{
					lineItem.setSuggScheduleDate( StringUtils.formatDateToString( requestedDate, null ) );
					lineItem.setScheduleDate( StringUtils.formatDateToString( requestedDate, null ) );
					systemSuggestedDate = requestedDate;
					
					info.put( "Found quantity to meet the date :"+ StringUtils.formatDateToString ( requestedDate, null ), null );
					info.put( "Requested Schedule Date: " + requestedScheduleDate + ", System Suggested Date: " + lineItem.getSuggScheduleDate(), null );
					info.put( "NOTE: These lots will not be reserved until save this schedule date", null );
					hasRequiredQty = true;
				}
				else	// Check if the Requested Quantity can be met with the Inventory available and Least Possiable date When it can be scheduled  
				{
					if( rs != null )	
						rs.close();
					rs = null;
					
					if( pstmt != null )
						pstmt.close();
					pstmt = null;
					
					availableQty = 0;
					
					//pstmt = conn.prepareStatement( "SELECT Sum( Fg_Qty - Qty_Allocated ) as Qty_Available from Wip_Master where Fg_ProcessStep_ID = ?" );
					pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.orderscheduling.all.availablequantity" ) );
					pstmt.setInt( 1, fgProcessStep_Id );
					rs = pstmt.executeQuery();

					if( rs.next() )
					{
						availableQty = rs.getInt( "Qty_Available" );
					}
					
					if( availableQty >= requestedQty )
					{
						hasRequiredQty = true;
					}
					else // The Required cannot be met with the Inventory 
					{
						hasRequiredQty = false;
					}
				}
				
				if( systemSuggestedDate == null && hasRequiredQty == true )
				{
					if( rs != null ) {
						try { rs.close(); } catch( SQLException ignore ){}
						rs = null;
					}
					if( pstmt != null ) {
						try { pstmt.close(); } catch( SQLException ignore ){}
						pstmt = null;
					}
					if( conn != null ) {
						try { conn.close(); } catch( SQLException ignore ){}
						conn = null;
					}
					
					systemSuggestedDate = getPossiableScheduleDate( dbTransaction, fgProcessStep_Id, requestedQty );

                    sysSuggCal = new GregorianCalendar();
					sysSuggCal.setTime( systemSuggestedDate );
					//sysSuggCal.add( Calendar.DATE , 5 );
					
					lineItem.setSuggScheduleDate( StringUtils.formatDateToString ( sysSuggCal.getTime(), null ) );
					lineItem.setScheduleDate( StringUtils.formatDateToString ( sysSuggCal.getTime(), null ) );
					
					info.put( "There is not enough quantity to meet date:" + StringUtils.formatDateToString ( requestedDate, null ), null );
					info.put( "Requested Schedule Date: " + requestedScheduleDate + ", System Suggested Date: " + lineItem.getSuggScheduleDate(), null );
					info.put( "NOTE: These lots will not be reserved until save this schedule date", null );
				}
				else if( systemSuggestedDate == null && hasRequiredQty == false )
				{
					// Number of days from the Requested Dock Date and Present Date
					daysToRequestedDate = ( ( requestedDate.getTime() - now.getTime() ) /( 1000*60*60*24 ) );
					
					// The Time from Fab to Finished Goods
					transitTimeFromFabToFG = getTransitTimeFromFabToFG ( dbTransaction, fgProcessStep_Id );
					
					if( daysToRequestedDate >= transitTimeFromFabToFG ) 
					{
						lineItem.setSuggScheduleDate ( StringUtils.formatDateToString ( requestedDate, null ) );
						lineItem.setScheduleDate ( StringUtils.formatDateToString ( requestedDate, null ) );
						info.put( LineItem.REMARK_WAFERS_CAN_BE_STARTED + StringUtils.formatDateToString ( requestedDate, null ), null );
					}
					else
					{
                        sysSuggCal = new GregorianCalendar();
                        sysSuggCal.add( Calendar.DATE, transitTimeFromFabToFG);

                        lineItem.setSuggScheduleDate( StringUtils.formatDateToString ( sysSuggCal.getTime(), null ) );
                        lineItem.setScheduleDate( StringUtils.formatDateToString ( sysSuggCal.getTime(), null ) );

                        info.put( "There is not enough quantity to meet date", requestedScheduleDate );
                        info.put( LineItem.REMARK_WAFERS_CAN_BE_STARTED + lineItem.getSuggScheduleDate(), null );
                    }
				}
			}
			catch ( SQLException sql )
			{
				sql.printStackTrace();
				throw new ScalarException( "Could not fetch the schedule date for the Line Item", sql );
			}
		   
			finally
			{
				if( rs != null ){
					try { rs.close(); } catch( SQLException ignore ){}
				}
				if( pstmt != null ){
					try { pstmt.close(); } catch( SQLException ignore ){}
				}
				if( conn != null ){
					try { conn.close(); } catch( SQLException ignore ){}
				}
			}
			success = true;
		}
		catch ( ScalarException se )
		{
			se.printStackTrace();
			throw se;
		}
		finally 
		{
			if( success == true ) 
			{
				if( dbTransaction != null )
				{
					dbTransaction.commit();
				}
			}
			else 
			{
				if( dbTransaction != null ) 
				{
					dbTransaction.rollback();
				}
			}
		}

		return lineItem;
	}
	
	/**
	 * @param mktPartNumber
	 * @param lineItemNumber
	 * @param salesOrderId
	 * @return
	 */
	public static LineItem loadLineItem( String mktPartNumber, String lineItemNumber, String salesOrderId ) throws ScalarException
	{
		LineItem lineItem 	= null;
		int quantityToBeShipped = 0;
		
		try
        {
            lineItem = SalesOrderDAO.loadOrderLineItem ( lineItemNumber, salesOrderId );
			quantityToBeShipped = lineItem.getToBeShippedQty();
			lineItem.setQuantity( quantityToBeShipped );
		}
        catch ( ScalarException e )
        {
			e.printStackTrace();
        	throw e;
        } 
		return lineItem;
	}
	
    /**
     * @param dbTransaction
     * @param fgProcessStep_Id
     * @param requestedScheduleDate
     * @param requestedQty
     */
    private static Date getPossiableScheduleDate( DBTransaction dbTransaction, int fgProcessStep_Id, int requestedQty ) throws ScalarException 
    {
		Connection conn  		= null;
		ResultSet rs     		= null;
		PreparedStatement pstmt = null;
		int qty 	= 0;
		Date fgDateOut = null;
		int currQty = 0;
		Date suggDate = null;

		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			//pstmt = conn.prepareStatement( "SELECT ( Fg_Qty - Qty_Allocated ) as Qty_Available, Fg_date from Wip_Master where Fg_ProcessStep_ID = ? order by fg_date asc" );
			pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.orderscheduling.possiable.schedule.date" ) );
			pstmt.setInt( 1, fgProcessStep_Id );
			
			rs = pstmt.executeQuery();

			while( rs.next() )
			{
				qty = rs.getInt( "Qty_Available" );
				fgDateOut = rs.getDate( "Fg_date" );
				if( qty > 0 )
				{
					currQty = currQty + qty;
				}
				if( currQty >= requestedQty )
				{
					suggDate = fgDateOut;
					break;
				}
			}
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException( "Could not fetch possiable Schedule Date for the Sales Order", sql );
		}
		   
		finally
		{
			if ( rs != null ) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if ( pstmt != null ) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if ( conn != null ) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return suggDate;
    }


    /**
     * @param dbTransaction
     * @param fgProcessStep_Id
     * @return
     */
    /*private static LinkedList getAllAvailableLotList(DBTransaction dbTransaction, int fgProcessStep_Id) throws ScalarException
    {
		LinkedList availableLotList = null;
		Connection conn  = null;
		ResultSet rs     = null;
		PreparedStatement pstmt = null;
		LotNumberQtyAndDateOut lotInfo = null;
		int lotId = 0;
		int qty = 0;
		Date fgDateOut = null;

		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			
			//pstmt = conn.prepareStatement( "SELECT Lot_Id, ( Fg_Qty - Qty_Allocated ) as Qty_Available, Fg_date from Wip_Master where Fg_ProcessStep_ID = ? order by fg_date asc" );
			pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.orderscheduling.getAllLotList" ) );
			pstmt.setInt( 1, fgProcessStep_Id );
			
			rs = pstmt.executeQuery();

			while( rs.next() )
			{
				lotId = rs.getInt( "Lot_Id" );
				qty = rs.getInt( "Qty_Available" );
				fgDateOut = rs.getDate( "Fg_date" );
				if( qty > 0 )
				{
					lotInfo = new LotNumberQtyAndDateOut( lotId, qty, fgDateOut );
					availableLotList.add( lotInfo );
				}
			}
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException( "Could not fetch Wip information", sql );
		}
		   
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
        return availableLotList;
    }*/

    /**
     * @param lineItem
     */
    public static void saveSchedulelineItem( LineItem lineItem, LinkedHashMap info ) throws ScalarException
    {
		DBTransaction dbTransaction = null;
		int processStepId = 0;
		boolean success = false;
		
		try 
		{
			// Start transaction
			dbTransaction = DBTransaction.getInstance();
			dbTransaction.begin();
			
			processStepId = getFinshedGoodsProcessStepId( dbTransaction, lineItem.getProductId() );
			
			// Step 1
			// Release Old Lots
			releaseReservedLots( dbTransaction, lineItem.getLineItemNo(), lineItem.getSalesOrderId(), processStepId );
			
			// Step 2
			// Reserve lots only if the system suggested date and schedule date are same
			if( ( lineItem.getScheduleDate() != null ) && lineItem.getScheduleDate().trim().length() > 1 && StringUtils.getSQLDate( lineItem.getScheduleDate() ) != null )// && ( isValidScheduleDate( lineItem.getScheduleDate(), lineItem.getSuggScheduleDate() ) ) )
			{
				reserveLotsForLineItem( dbTransaction, lineItem, processStepId, info );
			}
			else
			{
                String suggScheduleDate = "";
                suggScheduleDate = lineItem.getSuggScheduleDate();
                if( suggScheduleDate == null )
                {
                    suggScheduleDate = "";
                }
				info.put ( "Schedule Date: <b>" + lineItem.getScheduleDate() + "</b> Differed from System Suggested Date: <b>" + suggScheduleDate + "</b>", null );
				info.put ( "All previously reserved lots (if any) are released and No new Lots are reserved", null );
			}
			
			// Step 3
			// Update System Suggested Date and schedule Date
			SalesOrderDAO.updateLineItemSuggestedDateAndSystemDate( dbTransaction, lineItem.getLineItemNo()+"", lineItem.getSalesOrderId()+"", lineItem.getSuggScheduleDate(), lineItem.getScheduleDate() );
			info.put ( "System Suggested Date and Schedule Date are updated successfully", null );
			success = true;
		}
		catch ( ScalarException se )
		{
			throw se;
		}
		finally 
		{
			if ( success == true ) 
			{
				if ( dbTransaction != null )
				{
					dbTransaction.commit();
				}
			}
			else 
			{
				if ( dbTransaction != null ) 
				{
					dbTransaction.rollback();
				}
			}
		}
    }


    /**
     * @param dbTransaction
     * @param string
     * @param string2
     */
    private static void releaseReservedLots( DBTransaction dbTransaction, int lineItemNo, int salesOrderId, int processStepId ) throws ScalarException
    {
		Connection conn  = null;
		ResultSet rs     = null;
		PreparedStatement pstmt = null;
		boolean success = false;

		try
		{
			if( dbTransaction != null )
			{
				conn = dbTransaction.getConnection();
			}
			else
			{
				conn = DataSource.getConnection();
			}
			
			pstmt = conn.prepareCall( "{Call sp_release_reserved_lots( ?, ?, ? )}" );
			pstmt.setInt( 1, lineItemNo );
			pstmt.setInt( 2, salesOrderId );
			pstmt.setInt( 3, processStepId );
			
			pstmt.execute();

			success = true;
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Release Lots for the LineItem" );	
		}
		finally
		{
			if( dbTransaction == null )
			{
				if( success )
				{
					if( conn != null ){
						try{
							conn.commit();	
						}
						catch( Exception e ){}
					}
				}
				else
				{
					if( conn != null ){
						try{
							conn.rollback();	
						}
						catch( Exception e ){}
					}
				}
			}
        	
			if ( rs != null ){
				try { rs.close(); } catch (SQLException ignore){}
			}

			if ( pstmt != null ){
				try { pstmt.close(); } catch (SQLException ignore){}
			}

			if ( conn != null ){
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
    }


    /**
     * @param dbTransaction
     * @param lineItem
     */
    private static void reserveLotsForLineItem( DBTransaction dbTransaction, LineItem lineItem, int processStepId, LinkedHashMap info ) throws ScalarException
    {
		Connection conn  = null;
		ResultSet rs     = null;
		PreparedStatement pstmt  = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		boolean success = false;
		LinkedList wipList = null;
		LotNumberQtyAndDateOut lotInfo = null;
		int reqQty  = 0;
		int currQty = 0;
		int lotQty  = 0;

        try
        {
        	// Step 1
        	// Get the WIP Lot information  	
			wipList = getWipInfoByLot( dbTransaction, lineItem.getProductId(), lineItem.getScheduleDate(), processStepId );
			reqQty = lineItem.getQuantity();
			if( dbTransaction != null )
			{
				conn = dbTransaction.getConnection();
			}
			else
			{
				conn = DataSource.getConnection();
			}
			
			//	Insert into Wip_Reservation_Table			
			//pstmt = conn.prepareStatement( "Insert into Wip_Reservation_Table ( SalesOrder_ID, Line_Item_No, Lot_Id, Qty_Allocated ) values ( ?, ?, ?, ? ) " );
			pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.orderscheduling.insertinto.WipReservation" ) );
			
			// Update Wip_Master with the Quantity Allocated
			//pstmt1 = conn.prepareStatement( "update Wip_Master set Qty_Allocated = Qty_Allocated + ( ( ( Fg_Qty * ? ) / ( select Fg_Qty from Temp_Wip_Master where Fg_processStep_Id = ? and lot_Id = ? ) ) ) where lot_Id = ?" );
			pstmt1 = conn.prepareStatement( SqlQueryMappings.get( "operations.orderscheduling.update.WipMaster" ) );
			
			// Update Backlog_Master with the sales order details
			//pstmt2 = conn.prepareStatement( "insert into Backlog_Master ( SalesOrder_Id, LineItem_No, ProcessStep_Id, Qty_Allocated, Schedule_Date ) values ( ?, ?, ?, ?, ? )" );
			pstmt2 = conn.prepareStatement( SqlQueryMappings.get( "operations.orderscheduling.update.BackLog" ) );
			reqQty = lineItem.getQuantity();
			
			if( wipList != null && wipList.isEmpty() == false )
			{
				for( int i = 0; i < wipList.size(); i++ )
				{
					lotInfo = (LotNumberQtyAndDateOut)wipList.get( i );
					
					if( lotInfo.getQuantity() > 0 )
					{
						if( ( reqQty - currQty ) >= lotInfo.getQuantity() ) // Reserve All the quantity in the Lot 
						{
							lotQty = lotInfo.getQuantity();
						}
						else // Reserve only the remaining quantity
						{
							lotQty = reqQty - currQty;
						}
						
						pstmt.setInt( 1, lineItem.getSalesOrderId() );
						pstmt.setInt( 2, lineItem.getLineItemNo() );
						pstmt.setInt( 3, lotInfo.getLotId() );
						pstmt.setInt( 4, lotQty );
						pstmt.addBatch();
			
						pstmt1.setInt( 1, lotQty );
						pstmt1.setInt( 2, processStepId );
						pstmt1.setInt( 3, lotInfo.getLotId() );
						pstmt1.setInt( 4, lotInfo.getLotId() );
						pstmt1.addBatch();
						
						currQty = currQty + lotQty;
					}
					if( reqQty <= currQty )
					{
						break;
					}
				}
			}
			
			// If the Quantites cannot be Allocated
			if( currQty < reqQty )
			{
				info.put( "Could not reserve the Quantity for the Schedule Date " + lineItem.getScheduleDate(), null );
			}
			else // Update the Wip_reservation_table and Wip_Master tables 
			{
				pstmt.executeBatch();
				pstmt1.executeBatch();
				info.put( "Lots are reserved for the Line Item for the Schedule Date "+ lineItem.getScheduleDate(), null );
			}

			// Insert into to BackLog_Master even if the Quantites cannot to Allocated		
			pstmt2.setInt( 1, lineItem.getSalesOrderId() );
			pstmt2.setInt( 2, lineItem.getLineItemNo() );
			pstmt2.setInt( 3, processStepId );
			pstmt2.setInt( 4, lineItem.getQuantity() );
			pstmt2.setDate( 5, StringUtils.getSQLDate( lineItem.getScheduleDate() ) );
			pstmt2.execute();

			success = true;
        }
        catch( Exception e )
        {
        	e.printStackTrace();
        	throw new ScalarException( "Could not Reserve Lots for the LineItem" );	
        }
        finally
        {
        	if( dbTransaction == null )
        	{
        		if( success )
        		{
        			if( conn != null ){
        				try{
        					conn.commit();	
        				}
        				catch( Exception e ){}
        			}
        		}
        		else
        		{
					if( conn != null ){
						try{
							conn.rollback();	
						}
						catch( Exception e ){}
					}
        		}
        	}
        	
			if ( rs != null ){
				try { rs.close(); } catch (SQLException ignore){}
			}

			if ( pstmt != null ){
				try { pstmt.close(); } catch (SQLException ignore){}
			}

			if ( pstmt1 != null ){
				try { pstmt1.close(); } catch (SQLException ignore){}
			}

			if ( pstmt2 != null ){
				try { pstmt2.close(); } catch (SQLException ignore){}
			}

			if ( conn != null ){
				try { conn.close(); } catch (SQLException ignore){}
			}
        }
    }


    /**
     * @param dbTransaction
     * @param i
     * @param string
     * @return
     */
    private static LinkedList getWipInfoByLot( DBTransaction dbTransaction, int productId, String scheduleDateStr, int processStepId ) throws ScalarException
    {
		Connection conn    = null;
		ResultSet rs       = null;
		PreparedStatement pstmt = null;
		
		LinkedList wipList = new LinkedList();
		Date scheduleDate  = null; 
		LotNumberQtyAndDateOut lotInfo = null;
		int lot_id = 0;
		int qty = 0;
		Date dateOut = null;
		
		try
		{
			scheduleDate 	= StringUtils.format( scheduleDateStr );
			
			if( dbTransaction != null )
			{
				conn = dbTransaction.getConnection();
			}
			else
			{
				conn = DataSource.getConnection();
			}
			
			//pstmt = conn.prepareStatement( "Select Lot_Id, Fg_Qty, Fg_Date from Wip_Master where Fg_ProcessStep_Id = ? and Fg_Date <= ? order by Fg_Date Desc" );
			pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.orderscheduling.fetch.WipInventory" ) );
			pstmt.setInt( 1, processStepId );
			pstmt.setDate( 2, StringUtils.getSQLDate( scheduleDate ) );
			
			rs = pstmt.executeQuery(); 
			
			while( rs.next() )
			{
				lot_id 	= rs.getInt ( "Lot_Id" );
				qty 	= rs.getInt ( "Fg_Qty" );
				dateOut = rs.getDate( "Fg_Date" );

				if( qty > 0 )
				{
					lotInfo = new LotNumberQtyAndDateOut( lot_id, qty, dateOut );
					wipList.add( lotInfo );
				}
			}
		}
		catch( Exception e )
		{
        	e.printStackTrace();
        	throw new ScalarException( "Could not fetch the Wip Lot information" );
		}
		finally
		{
			if ( rs != null ){
				try { rs.close(); } catch (SQLException ignore){}
			}

			if ( pstmt != null ){
				try { pstmt.close(); } catch (SQLException ignore){}
			}

			if ( conn != null ){
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
        return wipList;
    }

	/**
	 * @param fgProcessStep_Id
	 * @return
	 */
	private static int getTransitTimeFromFabToFG(  DBTransaction dbTransaction, int fgProcessStep_Id ) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		PreparedStatement pstmt = null;
		int fabProcessStepId = 0;
		int totalCycleTransitTime = 0;
		String path = null;
		
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			// Fecth the FAB ProcessStepID from the Finished Goods
			//pstmt = conn.prepareStatement( "Select Top 1 ProcessStep_ID from fn_get_process_flow_by_processstep_id( ? ) where Process_Type_Name = ? " );
			pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.orderscheduling.getFabProcess.for.FinishedGoods" ) );
			pstmt.setInt ( 1, fgProcessStep_Id );
			pstmt.setString( 2, LOCATION_TYPE_FAB );
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				fabProcessStepId = rs.getInt( "ProcessStep_ID" );
			}
			
			if( fabProcessStepId != 0 )
			{
				if (rs != null) {
					try { rs.close(); } catch (SQLException ignore) {}
				}
				if (pstmt != null) {
					try { pstmt.close(); } catch (SQLException ignore) {}
				}

                pstmt = conn.prepareStatement ( "SELECT TOP 1 TOTAL_CYCLE_TIME, FINAL_YIELD FROM WIP_FLUSH_TABLE WHERE PROCESSSTEP_ID = ?  AND FG_PROCESSSTEP_ID = ?" );
                pstmt.setInt ( 1, fabProcessStepId );
                pstmt.setInt ( 2, fgProcessStep_Id );
                rs = pstmt.executeQuery();
                if ( rs.next() )
                {
                    totalCycleTransitTime = rs.getInt ( "TOTAL_CYCLE_TIME" );
                }
                else
                {
                    if (rs != null) {
					try { rs.close(); } catch (SQLException ignore) {}
                    }
                    if (pstmt != null) {
                        try { pstmt.close(); } catch (SQLException ignore) {}
                    }

                    pstmt = conn.prepareStatement ( "{CALL SP_CREATE_WIP_FLUSH_SETUP(?)}" );
                    pstmt.setInt ( 1, fabProcessStepId );
                    pstmt.execute();

                    if (rs != null) {
					try { rs.close(); } catch (SQLException ignore) {}
                    }
                    if (pstmt != null) {
                        try { pstmt.close(); } catch (SQLException ignore) {}
                    }

                    pstmt = conn.prepareStatement ( "SELECT TOP 1 TOTAL_CYCLE_TIME, FINAL_YIELD FROM WIP_FLUSH_TABLE WHERE PROCESSSTEP_ID = ?  AND FG_PROCESSSTEP_ID = ?" );
                    pstmt.setInt ( 1, fabProcessStepId );
                    pstmt.setInt ( 2, fgProcessStep_Id );
                    rs = pstmt.executeQuery();
                    if ( rs.next() )
                    {
                        totalCycleTransitTime = rs.getInt ( "TOTAL_CYCLE_TIME" );
                    }
                }

                /*//pstmt = conn.prepareStatement( "SELECT Path, Last_Process_Step_ID FROM fn_ct_get_process_paths ( ? ) where Last_process_Step_ID = ?" );
				pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.orderscheduling.getPathFromFabToFinishedGoods" ) );
				pstmt.setInt( 1, fabProcessStepId );
				pstmt.setInt( 2, fgProcessStep_Id );
				rs = pstmt.executeQuery();
				
				if( rs.next() )
				{
					 path = rs.getString( "path" ); 
				}
				
				if( path != null )
				{
					if (rs != null) {
						try { rs.close(); } catch (SQLException ignore) {}
					}
					if (pstmt != null) {
						try { pstmt.close(); } catch (SQLException ignore) {}
					}

					//pstmt = conn.prepareStatement( "SELECT Total_Ship_Time, Total_Yield FROM fn_get_shiptime_yield( ?, ?, ?, ? ) " );
					pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.orderscheduling.getShipTimeAndYeildToFinishedGoods" ) );	
					pstmt.setInt( 1, fabProcessStepId );
					pstmt.setInt( 2, fgProcessStep_Id );
					pstmt.setString( 3, path );
					pstmt.setInt( 4, 0 );	// This is a flag to indicate it is not a Transit Lot
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						totalCycleTransitTime = rs.getInt( "Total_Ship_Time" ); 
					}
				}*/
			}
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException( "Could not fetch the Time from Fab To FG", sql );
		}
		   
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return totalCycleTransitTime;
	}

	public static int getFinshedGoodsProcessStepId ( DBTransaction dbTransaction, int productId ) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		int processStepId = 0;
		CallableStatement cstmt = null;
		
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			cstmt = conn.prepareCall( "SELECT dbo.fn_getShipping_fgProcessStep_id ( ? )" );
			cstmt.setInt ( 1, productId );
			rs = cstmt.executeQuery();
		
			if( rs.next() )
			{
				processStepId = rs.getInt( 1 );
			}
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException( "Could not fetch the Finished Goods ProcessStep ID", sql );
		}
		   
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (cstmt != null) {
				try { cstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return processStepId;
	}
	
    /**
     * @param lineItem
     */
	public static void releaseSchedulelineItem( LineItem lineItem )throws ScalarException
   	{
		DBTransaction dbTransaction = null;
	   	int processStepId = 0;
	  	boolean success = false;
		
	   	try 
	   	{
			// Start transaction
		   	dbTransaction = DBTransaction.getInstance();
		   	dbTransaction.begin();
			
		   	processStepId = getFinshedGoodsProcessStepId( dbTransaction, lineItem.getProductId() );
			
		   	// Step 1
		   	// Release Old Lots
		   	releaseReservedLots ( dbTransaction, lineItem.getLineItemNo(), lineItem.getSalesOrderId(), processStepId );
			
		   	// Step 2
		   	// Update System Suggested Date and schedule Date
		   	//SalesOrderDAO.updateLineItemSuggestedDateAndSystemDate( dbTransaction, lineItem.getLineItemNo()+"", lineItem.getSalesOrderId()+"", null, null );
            releaseLineItemScheduleDates( dbTransaction, lineItem.getSalesOrderId(), lineItem.getLineItemNo() );
            success = true;
		}
	   	catch ( Exception e )
	   	{
			e.printStackTrace();
			throw new ScalarException( "Could not replease the Lots" );
	   	}
	   	finally 
	   	{
			if ( success == true ) 
		   	{
				if ( dbTransaction != null )
			   	{
					dbTransaction.commit();
			   	}
		   	}
		   	else 
		   	{
				if ( dbTransaction != null ) 
			   	{
					dbTransaction.rollback();
			   	}
		   	}
	   	}
    }
    
	public static ArrayList loadAllScheduleUnits( String mfgPartNo ) throws ScalarException
	{
		ArrayList list 		= new ArrayList();
		int processStepId 	= -1;
		int shippingProcessStepId = -1;
		
		processStepId = ProductDao.getProcessStepIdByMfgPartNumber( mfgPartNo );
		shippingProcessStepId = ProductDao.getShippingProcessStepIdByMfgPartNumber( mfgPartNo );

		// Step 1
		// Get entire lot quantity
		Inventory entireLotInventory = getLotInventory( shippingProcessStepId, ENTIRE_LOT_QUANTITY, "Shippable Units" );
		list.add( entireLotInventory );

		// Step 2
		// Get issued lot quantity
		Inventory issuedLotInventory = getLotInventory( shippingProcessStepId, ISSUED_LOT_QUANTITY, "Issued Units" );
		list.add( issuedLotInventory );

		// Step 3
		// Get reserved lot quantity
		Inventory reservedLotInventory = getLotInventory( shippingProcessStepId, RESERVED_LOT_QUANTITY, "Reserved Units" );
		list.add( reservedLotInventory );

		// Step 4
		// Calculate Delta
		//Inventory deltaInventory = entireLotInventory.getDelta ( reservedLotInventory );
		Inventory deltaInventory = getDeltaInventory ( entireLotInventory, issuedLotInventory, reservedLotInventory );
		
		if( deltaInventory == null )
		{
			throw new ScalarException( "Error while loading schedule units" );
		}
			
		deltaInventory.setProcessType( "Delta Availability" );
		list.add( deltaInventory );

		// Step 5
		// Calculate Cumulative
		Inventory cumulativeInventory = deltaInventory.getCumulative();
		
		if( cumulativeInventory == null )
		{
			throw new ScalarException( "Error while loading schedule units" );
		}
			
		cumulativeInventory.setProcessType( "Cumulative Availability" );
		list.add( cumulativeInventory );

		// Step 5
		// Get backlog by schedule date
		Inventory backlogInventoryByScheduledDate = getBackLog( processStepId, BY_SCHEDULE_DATE, "B/L By sch. Date" );
		list.add( backlogInventoryByScheduledDate );

		// Step 6
		// Get backlog by requested date
		Inventory backlogInventoryByrequestedDate = getBackLog( processStepId, BY_REQUESTED_DATE, "B/L By req. Date" );
		list.add( backlogInventoryByrequestedDate );

		return list;
	}

	/**
	 * @param entireLotInventory
	 * @param reservedLotInventory
	 * @return
	 */
	private static Inventory getDeltaInventory( Inventory entireLotInventory, Inventory issuedLotInventory, Inventory reservedLotInventory )
	{
		Inventory lotInventory = new Inventory();
		
		lotInventory.setWeek1( entireLotInventory.getWeek1() - issuedLotInventory.getWeek1() - reservedLotInventory.getWeek1() );
		lotInventory.setWeek2( entireLotInventory.getWeek2() - issuedLotInventory.getWeek2() - reservedLotInventory.getWeek2() );
		lotInventory.setWeek3( entireLotInventory.getWeek3() - issuedLotInventory.getWeek3() - reservedLotInventory.getWeek3() );
		lotInventory.setWeek4( entireLotInventory.getWeek4() - issuedLotInventory.getWeek4() - reservedLotInventory.getWeek4() );
		lotInventory.setWeek5( entireLotInventory.getWeek5() - issuedLotInventory.getWeek5() - reservedLotInventory.getWeek5() );
		lotInventory.setWeek6( entireLotInventory.getWeek6() - issuedLotInventory.getWeek6() - reservedLotInventory.getWeek6() );
		lotInventory.setWeek7( entireLotInventory.getWeek7() - issuedLotInventory.getWeek7() - reservedLotInventory.getWeek7() );
		lotInventory.setWeek8( entireLotInventory.getWeek8() - issuedLotInventory.getWeek8() - reservedLotInventory.getWeek8() );
		lotInventory.setWeek9( entireLotInventory.getWeek9() - issuedLotInventory.getWeek9() - reservedLotInventory.getWeek9() );
		lotInventory.setWeek10( entireLotInventory.getWeek10() - issuedLotInventory.getWeek10() - reservedLotInventory.getWeek10() );
		lotInventory.setWeek11( entireLotInventory.getWeek11() - issuedLotInventory.getWeek11() - reservedLotInventory.getWeek11() );
		lotInventory.setWeek12( entireLotInventory.getWeek12() - issuedLotInventory.getWeek12() - reservedLotInventory.getWeek12() );
		lotInventory.setWeek13( entireLotInventory.getWeek13() - issuedLotInventory.getWeek13() - reservedLotInventory.getWeek13() );

		return lotInventory;
	}

	private static Inventory getLotInventory( int processStepId, int qtyCriteria, String processType ) throws ScalarException 
	{
		Inventory lotInventory = new Inventory();
		lotInventory.setProcessType( processType );

		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rset 			= null;
		
		try 
		{
			conn = DataSource.getConnection();

			if( qtyCriteria == RESERVED_LOT_QUANTITY ) 
			{
				pstmt =  conn.prepareStatement( "select Qty_allocated as Fg_Qty, Fg_Date from Wip_Master, Lot_table, Process_Step where Wip_Master.Lot_Id = Lot_table.Lot_id and Process_Step.ProcessStep_id = Wip_Master.ProcessStep_id and Lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'Hold' and Fg_ProcessStep_id = ?" );
				pstmt.setInt( 1, processStepId );
			}
			else if( qtyCriteria == ENTIRE_LOT_QUANTITY ) 
			{
				pstmt =  conn.prepareStatement( "select Fg_Qty, Fg_Date from Wip_Master, Lot_table, Process_Step where Wip_Master.Lot_Id = Lot_table.Lot_id and Process_Step.ProcessStep_id = Wip_Master.ProcessStep_id and Lot_table.Lot_Manual_Hold = 1 and Lot_table.Lot_Current_Status <> 'Hold' and Fg_ProcessStep_id = ?" );
				pstmt.setInt( 1, processStepId );
			}
			else if( qtyCriteria == ISSUED_LOT_QUANTITY ) 
			{
				pstmt =  conn.prepareStatement( "select SUM(CSLNT_Qty_Pull) as Fg_Qty, getdate() as Fg_date from Customer_shipping_lot_number_table, Lot_table where Customer_shipping_lot_number_table.Lot_id = Lot_table.Lot_id and ProcessStep_id = ? and CSLNT_Status is null" );
				pstmt.setInt( 1, processStepId );
			}
			else 
			{
				throw new ScalarException( "OrderSchedulingDAO.getLotQuantityByWeek() - Invalid Quantity Criteria" );
			}

			int lotQuantity 		= 0;
			java.sql.Date expectedDateOut = null;
			long daysRemainingToExpectedDateOut = 0;
			int weekNumber 		= 0;

			rset = pstmt.executeQuery();
			
			while( rset.next() ) 
			{
				lotQuantity 	= rset.getInt( "Fg_Qty" );
				expectedDateOut = rset.getDate( "Fg_Date" );

				if( expectedDateOut == null ) 
				{
					continue;
				}

				Calendar nowCal = new GregorianCalendar();
				nowCal.setTime( new Date() );
				Date now = new GregorianCalendar( nowCal.get( Calendar.YEAR ), nowCal.get( Calendar.MONTH ), nowCal.get( Calendar.DATE ) ).getTime();

				daysRemainingToExpectedDateOut = ( ( expectedDateOut.getTime() - now.getTime() )/( 1000*60*60*24 ) );
				
				if( daysRemainingToExpectedDateOut < 0 ) 
				{
					weekNumber = 1;
				}
				else 
				{
					weekNumber = (int)( daysRemainingToExpectedDateOut/7 ) + 1;
				}
				
				setLotQty( lotInventory, weekNumber, lotQuantity ); 
			}
		}
		catch (Exception se) 
		{
			se.printStackTrace();
			throw new ScalarException( "Exception while trying to get lot quantity for mfg Partnumber: " + processStepId, se );
		}
		finally 
		{
			if( rset != null ) 
			{
				try { rset.close(); } catch (SQLException ignore){}
			}

			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}

			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
	   	}
		return lotInventory;
	}

	/**
	 * @param lotInventory
	 * @param weekNumber
	 * @param lotQuantity
	 */
	private static void setLotQty( Inventory lotInventory, int weekNumber, int lotQuantity )
	{
		switch ( weekNumber )
		{
			case 1:
				lotInventory.setWeek1( lotInventory.getWeek1() + lotQuantity );
				break;
			case 2:
				lotInventory.setWeek2( lotInventory.getWeek2() + lotQuantity );
				break;
			case 3:
				lotInventory.setWeek3( lotInventory.getWeek3() + lotQuantity );
				break;
			case 4:
				lotInventory.setWeek4( lotInventory.getWeek4() + lotQuantity );
				break;
			case 5:
				lotInventory.setWeek5( lotInventory.getWeek5() + lotQuantity );
				break;
			case 6:
				lotInventory.setWeek6( lotInventory.getWeek6() + lotQuantity );
				break;
			case 7:
				lotInventory.setWeek7( lotInventory.getWeek7() + lotQuantity );
				break;
			case 8:
				lotInventory.setWeek8( lotInventory.getWeek8() + lotQuantity );
				break;
			case 9:
				lotInventory.setWeek9( lotInventory.getWeek9() + lotQuantity );
				break;
			case 10:
				lotInventory.setWeek10( lotInventory.getWeek10() + lotQuantity );
				break;
			case 11:
				lotInventory.setWeek11( lotInventory.getWeek11() + lotQuantity );
				break;
			case 12:
				lotInventory.setWeek12( lotInventory.getWeek12() + lotQuantity );
				break;
			case 13:
				lotInventory.setWeek13( lotInventory.getWeek13() + lotQuantity );
				break;
		}
	}

	// Added by Balaram
	private static Inventory getBackLog( int processStepId, int dateCriteria, String processType) throws ScalarException 
	{
		Inventory backLogInventory = new Inventory();
		backLogInventory.setProcessType (processType);
		Connection conn 		= null;
		CallableStatement cstmt = null;
		ResultSet rs 			= null;
		
		try 
		{
			conn = DataSource.getConnection();

			if( dateCriteria == BY_SCHEDULE_DATE ) 
			{
				cstmt = conn.prepareCall( "select ISNULL( ISNULL(COOL_REVISED_DATE,COOL_Schedule_Date), COOL_REQUESTED_DATE ) as COOL_Date, COOL_Qty, ISNULL(( Select SUM( CSLIT_Qty_Shipped ) from Customer_Shipping_line_item_table where LineItem_Id = COOL.LineItem_ID and CSLIT_Status = 'SHIPPED' ), 0 ) as Qty_Shipped from Customer_Open_Order_LineItem COOL where Product_ID = ( Select Product_ID from product where ProcessStep_ID = ? ) and COOL_Status <> 'SHIPPED'" );
			}
			else if( dateCriteria == BY_REQUESTED_DATE ) 
			{
				cstmt = conn.prepareCall( "select COOL_REQUESTED_DATE as COOL_Date, COOL_Qty, ISNULL(( Select SUM( CSLIT_Qty_Shipped ) from Customer_Shipping_line_item_table where LineItem_Id = COOL.LineItem_ID and CSLIT_Status = 'SHIPPED' ), 0 ) as Qty_Shipped from Customer_Open_Order_LineItem COOL where Product_ID = ( Select Product_ID from product where ProcessStep_ID = ? ) and COOL_Status <> 'SHIPPED'" );
			}
			else 
			{
				throw new ScalarException( "SalesDAO.getBackLogByWeek() - Invalid Date Criteria" );
			}

			cstmt.setInt( 1, processStepId );
			rs = cstmt.executeQuery();
			
			java.sql.Date resultDate = null;
			int reqQty		= 0;
			int shippedQty	= 0;
			long days 		= 0;
			int weekNumber 	= 0;
			
			Calendar nowCal = new GregorianCalendar();
			nowCal.setTime( new Date() );
			Date now = new GregorianCalendar( nowCal.get( Calendar.YEAR ), nowCal.get( Calendar.MONTH ), nowCal.get( Calendar.DATE ) ).getTime();


			while( rs.next() ) 
			{
				resultDate = rs.getDate( "COOL_Date" );
				if( resultDate == null ) 
				{
					continue;
				}

				reqQty 		= rs.getInt( "COOL_Qty" );
				shippedQty	= rs.getInt( "Qty_Shipped" );

				days = ( ( resultDate.getTime() - now.getTime() ) / ( 1000*60*60*24 ) );
				
				if( days < 0 ) 
				{
					weekNumber = 1;
				}
				else
				{
					weekNumber = (int)(days/7) + 1;	
				}
				
				setLotQty( backLogInventory, weekNumber, reqQty - shippedQty );
			}
		}
		catch (Exception se) 
		{
			se.printStackTrace();
			throw new ScalarException ("Exception while trying to get backlog information for " + processStepId, se);
		}
		finally 
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore){}
			}

			if (cstmt != null) {
				try { cstmt.close(); } catch (SQLException ignore){}
			}

			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
	   	}
		return backLogInventory;
	}

	public static ArrayList loadInventory( String mfgPartNo ) throws ScalarException
	{
		Connection conn 		= null;
	   	CallableStatement cstmt = null;
	   	ResultSet rs 			= null;
	   	ArrayList list 			= new ArrayList();
	   
		try 
	   	{

			conn = DataSource.getConnection();

			if( ( mfgPartNo != null ) && ( mfgPartNo.trim().length() != 0 ) ) 
			{
				cstmt =  conn.prepareCall( SqlQueryMappings.get( "sales.salesOrder.loadInventory" ) );
				cstmt.setString( 1, mfgPartNo );

				rs 		= cstmt.executeQuery();
				
				list 	= new ArrayList();
				Inventory inventory = null;
				
				while( rs.next() )
				{
					inventory = new Inventory();
					inventory.setProcessType( rs.getString( "Process" ) );
					inventory.setGoodUnits( rs.getInt( "Quantity" ) );
					inventory.setHoldUnits( rs.getInt( "Hold_Quantity" ) );

					list.add( inventory );
				}
			}
	   	} 
	   	catch (Exception se) 
	   	{
			se.printStackTrace();
	   	} 
	   	finally 
	   	{
			if(rs != null) {
				try { rs.close(); } catch (SQLException ignore){}
			}
			if (cstmt != null) {
				try { cstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
	   	}
	   	return list;
	}
	
	public static ArrayList getLotDetails( String mfgPartNo, String fromDate, String toDate ) throws ScalarException
	{
		ArrayList lotDetails 	= new ArrayList();
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rset 			= null;
		int processStepId		= 0;
		LotNumberQtyAndDateOut lot = null;
		
		processStepId = ProductDao.getShippingProcessStepIdByMfgPartNumber( mfgPartNo );
		
		if( processStepId == -1 )
		{
			return lotDetails;
		}
		 
		try 
		{
			conn = DataSource.getConnection();

			if( ( mfgPartNo != null ) && ( mfgPartNo.trim().length() != 0 ) ) 
			{
				pstmt =  conn.prepareStatement( "select wip.Lot_Number, Fg_Qty, CASE when ( Fg_date < getDate() ) then getdate() else Fg_date end as Fg_date, ( Select PS_Type_Name from Process_Step where ProcessStep_Id = wip.ProcessStep_Id ) as ps_type, ( Select CASE PS_Type WHEN 'FAB' then 10  WHEN 'Wafer Bank' then 9   WHEN 'Fab Option' then 8 WHEN 'Die Bank' then 7  WHEN 'Assembly' then 6   WHEN 'FT' then 5   WHEN 'FPO' then 4 WHEN 'Tape & Reel' then 3  WHEN 'FG' then 2 else 1 end from Process_Step where ProcessStep_Id = wip.ProcessStep_Id ) as Flag,  Qty_Allocated, ISNULL( ( Select sum( CSLNT_Qty_Pull ) from Customer_Shipping_lot_number_table where CSLNT_Status is NULL and Lot_Id = wip.Lot_Id ), 0 ) as Qty_Shipped from Wip_Master wip, Lot_Table where wip.Lot_Id = Lot_Table.Lot_Id and Lot_Manual_Hold = 1 and Lot_Current_Status <> 'Hold' and Fg_ProcessStep_Id = ?  and CASE when ( Fg_date < getDate() ) then getdate() else Fg_date end <= ? and CASE when ( Fg_date < getDate() ) then getdate() else Fg_date end >= ? ORDER BY FLAG, Fg_date" );
				pstmt.setInt( 1, processStepId );
				pstmt.setString( 2, toDate );
				pstmt.setString( 3, fromDate );

				rset = pstmt.executeQuery();
				
				while( rset.next() )
				{
					lot = new LotNumberQtyAndDateOut();
					lot.setLotNumber( rset.getString( "Lot_Number" ) );
					lot.setQuantity( rset.getInt( "Fg_Qty" ) );
					lot.setExpectedDateOut( rset.getDate( "Fg_Date" ) );
					lot.setProcessType( rset.getString( "ps_type" ) );
					lot.setReservedQty( rset.getInt( "Qty_Allocated" ) );
					lot.setIssuedQty( rset.getInt( "Qty_Shipped" ) );
					lotDetails.add( lot );
				}
			}
		} 
		catch (Exception se) 
		{
			se.printStackTrace();
			throw new ScalarException( "Could not fetch the Lot Details for the Product "+ mfgPartNo );
		} 
		finally 
		{
			if(rset != null) {
				try { rset.close(); } catch (SQLException ignore){}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
		return lotDetails; 
	}
	
	public static ArrayList getBackLogDetails( String mfgPartNo, String fromDate, String toDate) throws ScalarException
	{
		ArrayList lineItemDetails 	= new ArrayList();
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rset 			= null;
		int processStepId		= 0;
		LineItem lineItem 		= null;
		int reqQty				= 0;
		int shippedQty			= 0;
		
		processStepId = ProductDao.getProcessStepIdByMfgPartNumber( mfgPartNo ); 
		try 
		{
			conn = DataSource.getConnection();

			if( ( mfgPartNo != null ) && ( mfgPartNo.trim().length() != 0 ) ) 
			{
				pstmt =  conn.prepareStatement( "select SalesOrder_No, Cool_Line_item_No, C_Name, COOL_Qty, convert( varchar, ISNULL( ISNULL(COOL_REVISED_DATE,COOL_Schedule_Date), COOL_REQUESTED_DATE ), 101 ) as COOL_Date, ISNULL(( Select SUM( CSLIT_Qty_Shipped ) from Customer_Shipping_line_item_table where LineItem_Id = COOL.LineItem_ID and CSLIT_Status = 'SHIPPED' ), 0 ) as Qty_Shipped from Customer_Open_Order_LineItem COOL, Sales_Order, Customer where COOL.SalesOrder_Id = Sales_Order.SalesOrder_Id and Sales_order.Customer_ID = Customer.Customer_Id and COOL_Status <> 'SHIPPED' and Product_id = ( Select Product_Id from Product where ProcessStep_Id = ? ) and CASE when ( ISNULL( ISNULL(COOL_REVISED_DATE,COOL_Schedule_Date), COOL_REQUESTED_DATE ) < getDate() ) then getdate() else ISNULL( ISNULL(COOL_REVISED_DATE,COOL_Schedule_Date), COOL_REQUESTED_DATE ) end >= ? and ISNULL( ISNULL(COOL_REVISED_DATE,COOL_Schedule_Date), COOL_REQUESTED_DATE ) <= ? " );
				pstmt.setInt( 1, processStepId );
				pstmt.setString( 2, fromDate );
				pstmt.setString( 3, toDate );

				rset = pstmt.executeQuery();

				while( rset.next() )
				{
					lineItem = new LineItem();
					lineItem.setSalesOrderNo( rset.getString( "SalesOrder_No" ) );
					lineItem.setLineItemNo( rset.getInt( "Cool_Line_item_No" ) );
					lineItem.setCustomerName( rset.getString( "C_Name" ) );
					reqQty = rset.getInt( "COOL_Qty" );
					lineItem.setScheduleDate( rset.getString( "COOL_Date" ) );
					shippedQty = rset.getInt( "Qty_Shipped" );
					lineItem.setQuantity( reqQty - shippedQty );
					lineItemDetails.add( lineItem );
				}
			}
		} 
		catch (Exception se) 
		{
			se.printStackTrace();
		} 
		finally 
		{
			if(rset != null) {
				try { rset.close(); } catch (SQLException ignore){}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
		return lineItemDetails; 
	}
	

    
    public static ArrayList reAllocateAllBackLog(DBTransaction dbTransaction, String lineItemIds) throws ScalarException
    {
		Connection conn  = null;
		ResultSet rs     = null;
		CallableStatement cstmt = null;
		boolean success = false;
		ArrayList lineItemList = new ArrayList();
		LineItem lineItem = null;
		try
		{
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall( SqlQueryMappings.get( "operations.orderscheduling.reallocatesLotsToBackLog" ) );
            cstmt.setString ( 1, lineItemIds );
            rs = cstmt.executeQuery();
		
			while( rs.next() )
			{
				lineItem = new LineItem();
				lineItem.setSalesOrderNo( rs.getString( "SALESORDER_NO" ) );
				lineItem.setLineItemNo( rs.getInt( "LINEITEM_NO" ) );
				lineItem.setCustomerName( rs.getString( "CUSTOMER_NAME" ) );
				lineItem.setManufacturingPartNo( rs.getString( "MFG_PART_NUMBER" ) );
				lineItem.setQuantity( rs.getInt( "QTY_ALLOCATED" ) );
				lineItem.setBookingDate( StringUtils.formatDateToString( rs.getDate( "BOOKING_DATE" ) ) );
				lineItem.setRequestDate( StringUtils.formatDateToString( rs.getDate( "REQ_DATE" ) ) );
				lineItem.setScheduleDate( StringUtils.formatDateToString( rs.getDate( "SCHEDULE_DATE" ) ) );
                lineItem.setReason( rs.getString ( "REMARKS" ) );
                lineItemList.add( lineItem );
			}
			
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    conn.commit();
                }
            }
            success = true;
		}
		catch ( Exception e )
		{
            throw new ScalarException ( "Could not ReAllocate Scheduled LineItems", e );
        }
		finally
		{
            if ( !success )
            {
                if ( dbTransaction == null && conn != null )
                {
                    try { conn.rollback(); } catch ( SQLException ignore ) {}
                }
            }
            if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (cstmt != null) {
				try { cstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
        return lineItemList;
    }
    
    public static void main( String args[] )
    {
		try
        {
            reAllocateAllBackLog(null, null);
        }
        catch (ScalarException e)
        {
            e.printStackTrace();
        }
    }
    public static void releaseLineItemScheduleDates ( DBTransaction dbTransaction, int salesOrderId, int lineItemNo ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET COOL_SUGG_SCHEDULE_DATE_WITHOUT_SUBSTITUTABLES = NULL, COOL_REVISED_DATE = NULL, COOL_Schedule_Date = NULL WHERE SALESORDER_ID = ? AND COOL_LINE_ITEM_NO = ?" );
            pstmt.setInt ( 1, salesOrderId );
            pstmt.setInt ( 2, lineItemNo );
            pstmt.executeUpdate();

            try
            {
                pstmt.close();
            } catch ( SQLException ignore ) {}
            
            pstmt = conn.prepareStatement ( "UPDATE CUSTOMER_CHANGE_ORDER_LINEITEM SET CCO_SCHEDULE_DATE = NULL, CCO_REVISED_DATE = NULL WHERE CHANGEITEM_ID = ( SELECT CHANGEITEM_ID FROM CUSTOMER_CHANGE_ORDER_LINEITEM WHERE CHANGENO = ( SELECT MAX(CHANGENO) FROM CUSTOMER_CHANGE_ORDER_LINEITEM WHERE CCO_LINE_ITEM_NO = ? AND SALESORDER_ID = ? ) AND CCO_LINE_ITEM_NO = ? AND SALESORDER_ID = ? )" );
            pstmt.setInt ( 1, lineItemNo );
            pstmt.setInt ( 2, salesOrderId );
            pstmt.setInt ( 3, lineItemNo );
            pstmt.setInt ( 4, salesOrderId );
            pstmt.executeUpdate();

            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    conn.commit();
                }
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not release line item Schedule dates", e );
        }
        finally
        {
            if ( !success )
            {
                if ( dbTransaction == null && conn != null )
                {
                    try { conn.rollback(); } catch ( SQLException ignore ) {}
                }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try {  conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }

}